package com.chiao.dao.admin;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.chiao.core.BaseDao;
import com.chiao.core.CommonUtils;
import com.chiao.entity.admin.Employee;

@Repository
public class EmployeeDao extends BaseDao{

	private static final String INSERT_EMP_SQL="INSERT INTO employee(empno, empname, emppass, sex, tel, idcard, birthday, address, headpic, status, groupid, deptid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String UPDATE_EMP_SQL="UPDATE employee SET empno=?, empname=?, sex=?, tel=?, idcard=?, birthday=?, address=?, headpic=?, status=?, groupid=?, deptid=? WHERE empid=?";
	private static final String SELECT_EMP_SQL="SELECT * FROM EMPLOYEE WHERE EMPNO=?";
	
	
	public List<Map<String,Object>> queryEmployee(int start,int limit,String param){
		StringBuilder sb=new StringBuilder();
		sb.append("SELECT e.*,d.deptname FROM EMPLOYEE e ,dept d where e.deptid=d.deptid ");
		if(!CommonUtils.isBlank(param)){
			sb.append(" and (empname=? or empno=? )");
		}
		sb.append("order by empid,deptid limit ").append(limit).append(" offset ").append(start);
		if(CommonUtils.isBlank(param)){
			return queryForList(sb.toString());
		}else{
			return queryForList(sb.toString(),param,param);
		}
		
	}
	
	public int countEmployee(String param){
		String sql="SELECT COUNT(1) FROM EMPLOYEE ";
		if(CommonUtils.isBlank(param)){
			return queryForInt(sql);
		}else{
			sql+="where empname=?";
			return queryForInt(sql,param);
		}
		
	}

	/**
	 * 根据部门ID查询下面的员工
	 * @param deptid
	 * @return
	 */
	public int countEmployeeByDept(int deptid) {
		String sql="select count(1) from employee where deptid in (WITH RECURSIVE r AS (select d1.deptid from dept d1  where  d1.deptid=? union ALL SELECT DEPT.deptid FROM DEPT, r WHERE DEPT.pid = r.deptid) SELECT deptid FROM r)";
		return queryForInt(sql, deptid);
	}

	public List<Map<String, Object>> queryEmpByDept(int start, int limit, int deptid) {
		StringBuilder sb=new StringBuilder();
		sb.append("SELECT e.*,d.deptname FROM EMPLOYEE e ,dept d where e.deptid=d.deptid ");
		sb.append(" and e.deptid in (");
		sb.append("WITH RECURSIVE r AS (select d1.deptid from dept d1  where  d1.deptid=? union ALL SELECT DEPT.deptid FROM DEPT, r WHERE DEPT.pid = r.deptid) SELECT deptid FROM r");
		sb.append(") order by empid,deptid limit ").append(limit).append(" offset ").append(start);
		return queryForList(sb.toString(),deptid);
	}

	public int addEmp(Employee emp) {
		return update(INSERT_EMP_SQL,emp.getEmpno(),emp.getEmpname(),emp.getEmppass(),emp.getSex(),emp.getTel(),emp.getIdcard(),emp.getBirthday(),emp.getAddress(),emp.getHeadpic(),emp.isStatus(),emp.getGroupid(),emp.getDeptid());
	}

	public int updateEmp(Employee emp) {
		return update(UPDATE_EMP_SQL,emp.getEmpno(),emp.getEmpname(),emp.getSex(),emp.getTel(),emp.getIdcard(),emp.getBirthday(),emp.getAddress(),emp.getHeadpic(),emp.isStatus(),emp.getGroupid(),emp.getDeptid(),emp.getEmpid());
	}

	public int deleteEmpByIds(String ids) {
		String sql="delete from employee where empid in("+ids+")";
		return update(sql);
	}
        
        public Employee queryEmpByEmpno(String empno){
            return queryForBean( Employee.class,SELECT_EMP_SQL, empno);
        }
}
